/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package control.dao;

import model.Locacao;
import model.Operacao;

import BD.CriaConexao;
import IRepositorios.IRepositorioOperacao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Andre
 */
public class metodosOperacao implements IRepositorioOperacao{
private Connection conexao;

    public metodosOperacao() {
        try {
            this.conexao= CriaConexao.getConexao();
        } catch (SQLException ex) {
            Logger.getLogger(metodosOperacao.class.getName()).log(Level.SEVERE, null, ex);
        }
    }


   

   
    @Override
    public List<Locacao> buscarLocacoes(long cpf) {
       List<Locacao> minhaLista = new ArrayList<>();
        String sql="select * from operacao, locacao where id_op=id_locacao and ativo=true and cpf_op=?";
        PreparedStatement stmt;
        try {
            stmt = conexao.prepareStatement(sql);
       
            stmt.setLong(1, cpf);
        ResultSet rs = stmt.executeQuery();
        while(rs.next()){
            Locacao loc = new Locacao( cpf, rs.getInt("codigo_op"),0);
            loc.setData(rs.getDate("data"));
            loc.setAtivo(rs.getBoolean("ativo"));
            minhaLista.add(loc);
            }
            rs.close();
            stmt.close();
             } catch (SQLException ex) {
            Logger.getLogger(metodosFilme.class.getName()).log(Level.SEVERE, null, ex);
        }
        return minhaLista;
    }

    @Override
    public void deletarReserva(long cpf, int codigo) {
        String sql = " update operacao set ativo=? where cpf_op=? and codigo_op=?";
        PreparedStatement stmt;
        try {
            stmt = conexao.prepareStatement(sql);
       
        
        stmt.setBoolean(1, false);
        stmt.setLong(2, cpf);
        stmt.setInt(3, codigo);
        stmt.execute();
        stmt.close();
         } catch (SQLException ex) {
            Logger.getLogger(metodosOperacao.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public void deletarLocacao(long cpf, int codigo) {
       String sql = " update locacao set localcao_status=? where cod_cliente_loc=? and cod_dvd_loc=?";
        PreparedStatement stmt;
        try {
            stmt = conexao.prepareStatement(sql);
       
        
        stmt.setBoolean(1, false);
        stmt.setLong(2, cpf);
        stmt.setInt(3, codigo);
        stmt.execute();
        stmt.close();
         } catch (SQLException ex) {
            Logger.getLogger(metodosOperacao.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public List<Locacao> listaLocacoes(long cpf) {
        String sql="select * from operacao, locacao where id_op=id_locacao and cpf_op=?";
        List<Locacao> minhaLista= new ArrayList<>();
        try {
            PreparedStatement stmt= conexao.prepareStatement(sql);
            stmt.setLong(1, cpf);
            ResultSet rs= stmt.executeQuery();
            while(rs.next()){
                Locacao loc= new Locacao(rs.getLong("cpf_op"), rs.getInt("codigo_op"),0);
                loc.setAtivo(rs.getBoolean("ativo"));
                loc.setData(rs.getDate("data"));
                minhaLista.add(loc);
            }
            rs.close();
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(metodosOperacao.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return minhaLista;
    }

    @Override
    public int numeroLocacoes(long cpf) {
        String sql="select count(id_op) from operacao, locacao where id_locacao= id_op and cpf_op=?";
        int num=0;
        try {
            PreparedStatement stmt= conexao.prepareStatement(sql);
            stmt.setLong(1, cpf);
            ResultSet rs= stmt.executeQuery();
            while(rs.next()){
                num=rs.getInt("count");
            }
            rs.close();
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(metodosOperacao.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return num;
    }

    @Override
    public int numeroLocacoes(int codigo) {
        String sql="select count(id_op) from operacao, locacao where id_locacao= id_op and codigo_op=?";
        int num=0;
        try {
            PreparedStatement stmt= conexao.prepareStatement(sql);
            stmt.setInt(1, codigo);
            ResultSet rs= stmt.executeQuery();
            while(rs.next()){
                num=rs.getInt("count");
            }
            rs.close();
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(metodosOperacao.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return num;
    }

    @Override
    public int numeroLocacoesAtivas(long cpf) {
        String sql="select count(id_op) from operacao, locacao where ativo=true and id_locacao= id_op and cpf_op=?";
        int num=0;
        try {
            PreparedStatement stmt= conexao.prepareStatement(sql);
            stmt.setLong(1, cpf);
            ResultSet rs= stmt.executeQuery();
            while(rs.next()){
                num=rs.getInt("count");
            }
            rs.close();
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(metodosOperacao.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return num;
    }

    @Override
    public int numeroLocacoesAtivas(int codigo) {
        String sql="select count(id_op) from operacao, locacao where ativo=true and id_locacao= id_op and codigo_op=?";
        int num=0;
        try {
            PreparedStatement stmt= conexao.prepareStatement(sql);
            stmt.setInt(1, codigo);
            ResultSet rs= stmt.executeQuery();
            while(rs.next()){
                num=rs.getInt("count");
            }
            rs.close();
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(metodosOperacao.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return num;
    }

    @Override
    public int numeroReservas(int codigo) {
        String sql="select count(id_op) from operacao, reserva where ativo=true and id_reserva= id_op and codigo_op=?";
        int num=0;
        try {
            PreparedStatement stmt= conexao.prepareStatement(sql);
            stmt.setInt(1, codigo);
            ResultSet rs= stmt.executeQuery();
            while(rs.next()){
                num=rs.getInt("count");
            }
            rs.close();
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(metodosOperacao.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return num;
    }
    
    public int buscarID() {
        int id=0;
        String sql="SELECT MAX(id_op) FROM operacao";
        PreparedStatement stmt;
        try {
            stmt = conexao.prepareStatement(sql);
       
       
        ResultSet rs = stmt.executeQuery();
        while(rs.next()){
            
            id=rs.getInt("MAX");
            }
            rs.close();
            stmt.close();
             } catch (SQLException ex) {
            Logger.getLogger(metodosFilme.class.getName()).log(Level.SEVERE, null, ex);
        }
        return id;
    }

    @Override
    public void cadastrar(Locacao locacao) {
        String sql = "insert into locacao (data_locacao, data_devolucao, "
                + "localcao_status, cod_cliente_loc, cod_dvd_loc) values (?, ?, ?, ?, ?)";
        
        try {
            try (PreparedStatement stmt = conexao.prepareStatement(sql)) {
                java.sql.Date sqlDate = new java.sql.Date(locacao.getData().getTime());
                java.sql.Date sqlDateDevolucao = new java.sql.Date(locacao.getDataDevolucao().getTime());  
                stmt.setDate(1, sqlDate);
                stmt.setDate(2, sqlDateDevolucao);
                stmt.setLong(4, locacao.getCpf());
                stmt.setInt(5, locacao.getCodigoDvd());
                stmt.setBoolean(3, locacao.isAtivo());
                stmt.execute();
            }
        } catch (SQLException ex) {
            Logger.getLogger(metodosOperacao.class.getName()).log(Level.SEVERE, null, ex);
        }
       
    }

   
    @Override
    public List<Locacao> listaLocacoesCliente(String nome) {
       String sql="select c.cpf_cli, f.cod_filme, d.cod_dvd, l.localcao_status, l.data_locacao from dvd d, locacao l, cliente c, filme f"
               + " where c.nome like ? and d.cod_dvd = l.cod_dvd_loc and c.cpf_cli = l.cod_cliente_loc"
               + " and f.cod_filme = d.cod_filme_dvd";
       List<Locacao> minhaLista = null;
        try {
            PreparedStatement stmt= conexao.prepareStatement(sql);
            stmt.setString(1, nome);
            ResultSet rs = stmt.executeQuery();
            minhaLista = new ArrayList<>();
            while(rs.next()){
            Locacao loc = new Locacao(rs.getLong("cpf_cli"), rs.getInt("cod_filme"), rs.getInt("cod_dvd"));
            loc.setAtivo(rs.getBoolean("localcao_status"));
           
           
            loc.setData(rs.getDate("data_locacao"));
            minhaLista.add(loc);
            }
        } catch (SQLException ex) {
            Logger.getLogger(metodosOperacao.class.getName()).log(Level.SEVERE, null, ex);
        }
       return minhaLista;
    }

    @Override
    public Locacao buscarLocacao(long cpf, int codigo) {
       String sql="select * from locacao where cod_cliente_loc = ? and cod_dvd_loc = ? ";
        Locacao loc= new Locacao( cpf, codigo,0);
        try {
            PreparedStatement stmt= conexao.prepareStatement(sql);
            stmt.setInt(2, codigo);
            stmt.setLong(1, cpf);
            ResultSet rs= stmt.executeQuery();
            while(rs.next()){
               
                loc.setAtivo(rs.getBoolean("localcao_status"));
                System.out.println(rs.getBoolean("localcao_status"));
                loc.setData(rs.getDate("data_locacao"));
            }
            rs.close();
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(metodosOperacao.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return loc;
    }

    @Override
    public void cadastrar(Operacao operacao) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

  
   
    
}
